package com.yanyeori.webtest;

import com.yanyeori.framework.core.util.FileUtil;
import com.yanyeori.framework.core.util.StringUtil;
import com.yanyeori.framework.core.util.excel.ExcelUtil;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StopWatch;

import java.util.ArrayList;
import java.util.List;

/**
 * @author chenkuan
 * @since 2021/7/28 028 16:36
 */
public class TmsExportGateWay {
    private final Logger logger = LoggerFactory.getLogger(getClass());

    private List<UrlBO> urlBOList = new ArrayList<>();

    private final String excelPath1 = "D:/test/融资网银接口.xlsx";
    private final String excelPath2 = "D:/test/融资柜面接口.xlsx";

    private final String exportSql = "D:/test/";

    private final String gateWayName = "GATEWAY_URL_ROUTE.sql";
    private final String resourceName = "RESOURCE.sql";

    private final String appId = "198";

    private final int idStart = 18345;

    @Before
    public void readExcelBean() throws Exception {
        urlBOList = ExcelUtil.createReader()
                .setFilePath(excelPath2)
                .setSheetIndex(0)
                .setIndexField(0, "front_url")
                .setIndexField(1, "back_url")
                .setIndexField(2, "description")
                .setSkipAllEmptyColumnRow(true)
                .build()
                .read(UrlBO::new, 60, 60);
        for (UrlBO urlBO : urlBOList) {
            if (StringUtil.isBlank(urlBO.front_url)) {
                urlBO.front_url = urlBO.back_url;
            }
        }
    }

    @Test
    public void gatewayAndResource() {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        gateway();
        resource();
        stopWatch.stop();
        logger.info("导出完成，耗时={}ms", stopWatch.getTotalTimeMillis());
    }

    @Test
    public void gateway() {
        int id = idStart;

        StringBuilder deleteSql = new StringBuilder();
        deleteSql.append("DELETE FROM FMDBRUN.GATEWAY_URL_ROUTE WHERE FRONT_URL IN (");

        StringBuilder inSql = new StringBuilder();
        for (UrlBO urlBO : urlBOList) {
            deleteSql.append("'")
                    .append(urlBO.front_url)
                    .append("', ");

            inSql.append("INSERT INTO FMDBRUN.GATEWAY_URL_ROUTE(ID, APP_ID, FRONT_URL, BACK_URL, CONTENT_TYPE, DESCRIPTION, CREATE_TIME, UPDATE_TIME) VALUES (")
                    .append(id)
                    .append(", ")
                    .append(appId)
                    .append(", '")
                    .append(urlBO.front_url)
                    .append("', '")
                    .append(urlBO.back_url)
                    .append("', 'application/json;charset=UTF-8', '")
                    .append(urlBO.description)
                    .append("', NULL, NULL);");
            inSql.append("\n");
            id++;
        }
        String sql = deleteSql.substring(0, deleteSql.length() - 2);
        sql += ");\n";
        sql += inSql.toString();
        FileUtil.write(sql, exportSql, gateWayName, false);
    }

    @Test
    public void resource() {
        int id = idStart;

        StringBuilder deleteSql = new StringBuilder();
        deleteSql.append("DELETE FROM FMDBRUN.SYS_RESOURCE WHERE RES_URL IN (");

        StringBuilder inSql = new StringBuilder();
        for (UrlBO urlBO : urlBOList) {
            deleteSql.append("'")
                    .append(urlBO.front_url)
                    .append("', ");

            inSql.append("INSERT INTO FMDBRUN.SYS_RESOURCE(ID, RES_URL, RES_NAME, RES_TYPE, RES_POS, RES_CHK, STS_COD, REMARK, INSERT_DATE, UPDATE_DATE, SYS_CODE, HOST_URL, URL_PATH, CONTENT_TYPE) VALUES ('")
                    .append(id)
                    .append("', '")
                    .append(urlBO.front_url)
                    .append("', '")
                    .append(urlBO.description)
                    .append("', NULL, 610, '0', '0', NULL, NULL, NULL, NULL, '")
                    .append(appId)
                    .append("', '")
                    .append(urlBO.back_url)
                    .append("', 'application/json;charset=UTF-8');");
            inSql.append("\n");
            id++;
        }
        String sql = deleteSql.substring(0, deleteSql.length() - 2);
        sql += ");\n";
        sql += inSql.toString();
        FileUtil.write(sql, exportSql, resourceName, false);
    }

    class UrlBO {
        String front_url;
        String back_url;
        String description;
    }
}